from flask import Blueprint, render_template, request, redirect, url_for, session
import json
import pymysql
from config import Config
# from model.mapName import MapName
# from model.china_job import China_job
# from flask_sqlalchemy import SQLAlchemy
# from sqlalchemy import *
# db = SQLAlchemy()
ix = Blueprint('index', __name__)

'''
返回首页    
'''
@ix.route('/')
@ix.route('/index')
def index():
    connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD,
                              port=Config.MYSQL_PORT, database=Config.MYSQL_DATABASE)
    cursor = connect.cursor()
    user_ip = request.remote_addr
    cursor.execute('select visit from users_ip where ip = %s', (user_ip,))
    count = cursor.fetchall()
    # 如果ip不存在,就写进这个表
    if len(count) == 0:
        cursor.execute('insert into users_ip values(%s, 1)', (user_ip,))
        connect.commit()
    else:
        # 存在则取消首页样式, 并且记录访问次数
        cursor.execute('update users_ip set visit = %s where ip = %s', (count[0][0] + 1, user_ip))
        connect.commit()
        if int(count[0][0]) > 2:
            session['first'] = 1


    if session.get('power') is None:
        session['power'] = -404
    # 地图和柱状图
    # rows1 = db.session.query(China_job.province, (func.avg(func.SUBSTRING_INDEX(China_job.salary, "-", 1)
    #                                               +func.SUBSTRING_INDEX(China_job.salary, "-", -1))/2).label('a')
    #                                               )\
    #     .filter(China_job.salary != "面议").group_by(China_job.province).order_by('a').all()
    cursor.execute('select province, avg((substring_index(salary, "-", 1)+ substring_index(salary, "-", -1))/2)'
                   ' as a from china_job where salary != "面议" group by province order by a')
    rows1 = cursor.fetchall()
    map_data = []
    axis = []
    data = []
    values = list(map(lambda x: x[1], list(rows1)))
    map_max = int(max(values))
    map_min = int(min(values))
    # 柱图
    for row in rows1:
        axis.append(row[0])
        data.append(round(row[1], 2))
        map_data.append({'name': row[0], 'value': round(row[1], 2)})
    # 饼图

    # rows2 = db.session.query(China_job.position, func.count(China_job.province).label("c"))\
    #     .group_by(China_job.position).order_by(desc("c")).all()[:10]
    cursor.execute('select position,count(*) as c from china_job group by position order by c desc limit 10')
    rows2 = cursor.fetchall()
    pie_data1 = []
    pie_legend1 = []
    for row in rows2:
        pie_legend1.append(row[0])
        pie_data1.append({'name': row[0], 'value': row[1]})
    # rows3 = db.session.query(China_job.education, func.count(China_job.province)).group_by(China_job.education).all()
    cursor.execute('select education, count(province) from china_job where education != "不限" group by education')
    rows3 = cursor.fetchall()
    pie_data2 = {'高中': 0, '技校': 0, '中专': 0, '大专': 0, '本科': 0, '硕士': 0, '博士': 0}
    pie_legend2 = []
    for row in rows3:
        pie_legend2.append(row[0])
        pie_data2[row[0]] = row[1]
    title1 = 'TOP10工作类型占比'
    title2 = '学历类型占比'

    return render_template('index.html', map_data=map_data, map_max=map_max,
                           map_min=map_min, axis=axis, data=data,
                           pie_legend1=pie_legend1, pie_legend2=pie_legend2,
                           pie_data1=pie_data1, pie_data2=pie_data2,
                           title1=title1, title2=title2
                           )

'''
点击省份 跳转到省份分析界面
'''
@ix.route('/province')
def get_province():
    if request.method == 'GET':
        if session.get('power') == None:
            session['power'] = -404
        province = request.args.get('p')
        bar_axis = []
        bar_data = []
        bar_attr = {'name': '平均薪资', 'unit': '元'}
        connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD,
                                  port=Config.MYSQL_PORT, database=Config.MYSQL_DATABASE)
        cursor = connect.cursor()
        if province == None or province == '台湾' or province == '香港' or province == '南海诸岛':
            return redirect(url_for('index'))
        # rows = db.session.query(MapName.city).filter(MapName.province == province).all()

        cursor.execute('select city from map_name where province = %s', (province,))
        cities_rows = cursor.fetchall()
        if len(cities_rows) > 0:
            # 柱状图 所有城市的平均薪资
            bar_title = 'Top10城市平均薪资情况'
            # bar_rows = db.session.query(China_job.city,
            #                             (func.avg(func.SUBSTRING_INDEX(China_job.salary, "-", 1)
            #                              + func.SUBSTRING_INDEX(China_job.salary, "-", -1)) / 2).label('s')).\
            #             filter(China_job.province == province).group_by(China_job.city).order_by(desc("s")).all()[:10]
            cursor.execute('select city, avg((substring_index(salary, "-", 1)+substring_index(salary, "-", -1))/2) as s'
                           ' from china_job where province = %s group by city order by s desc limit 10', (province,))
            bar_rows = cursor.fetchall()
            for row in bar_rows:
                bar_axis.append(row[0])
                bar_data.append(round(row[1], 2))
            cities = list(map(lambda x: x[0], cities_rows))

            # 饼图 招聘数量前十五
            # pie_rows = db.session.query(China_job.position, func.count(China_job.position).label('c')).\
            #     group_by(China_job.position).filter(China_job.province == province).order_by(desc('c')).all()[:15]
            cursor.execute('select position, count(*) as c from china_job where province = %s group by position'
                           ' order by c desc limit 15', (province,))
            pie_rows = cursor.fetchall()
            pie_data = []
            pie_names = []
            pie_name = '岗位Top'
            pie_number = 0
            for row in pie_rows:
                pie_number += row[1]
                pie_names.append(row[0])
                pie_data.append(row[1])
            # 雷达图 计算各个学历的占比
            # radar_rows = db.session.query(China_job.education, func.count(China_job.education).label('c'),
            #                               China_job.city). \
            #     filter(China_job.province == province).group_by(China_job.city, China_job.education). \
            #     order_by('c').all()
            cursor.execute('select education, count(education) as c, city from china_job'
                           ' where province = %s group by city,education order by c', (province,))
            radar_rows = cursor.fetchall()
            radar_title = "学历与城市的关系"
            radar_indi = [
                {'name': '不限'},
                {'name': '本科'},
                {'name': '大专'},
                {'name': '中专'},
                {'name': '技校'},
                {'name': '高中'},
            ]
            radar_temp = {}
            for row in radar_rows:
                if row[2] in radar_temp.keys():
                    radar_temp[row[2]].append(row[1])
                else:
                    radar_temp[row[2]] = [row[1]]
            radar_legend = []
            radar_series = []

            for city in radar_temp:
                radar_legend.append(city)
                radar_series.append({
                    'value': radar_temp[city],
                    'name': city,
                    'itemStyle': {
                        'normal': {
                            'lineStyle': {
                                'color': '#4BFFFC',
                            },
                            'shadowColor': '#4BFFFC',
                            'shadowBlur': 10,
                        },
                    },
                    'areaStyle': {
                        'normal': {
                            'color': {
                                'type': 'linear',
                                'x': 0,
                                'y': 0,
                                'x2': 1,
                                'y2': 1,
                                'colorStops': [{
                                    'offset': 0,
                                    'color': '#4BFFFC'
                                }, {
                                    'offset': 0.5,
                                    'color': 'rgba(0,0,0,0)'
                                }, {
                                    'offset': 1,
                                    'color': '#4BFFFC'
                                }],
                                'globalCoord': 'false'
                            },
                            'opacity': 1
                        }
                    }
                })

            # 旋转图
            # xz_rows = db.session.query(func.count(China_job.position)). \
            #     filter(China_job.province == province).group_by(China_job.position).all()
            cursor.execute('select distinct(position) from china_job where province = %s', (province,))
            xz_rows = cursor.fetchall()
            number = len(xz_rows)
            number_format = "种岗位"

            # 漏斗图
            # funnel_data1 岗位个数
            funnel_data1 = []
            # funnel_data2 占比
            funnel_data2 = []
            funnel_title = '各个城市岗位种类数占比'
            funnel_name = '岗位种类数'
            # funnel_rows = db.session.query(China_job.city, func.count(China_job.position)).\
            #     filter(China_job.province == province).group_by(China_job.city).all()
            cursor.execute('select city, count(distinct(position)) from china_job where province = %s group by city',
                           (province,))
            funnel_rows = cursor.fetchall()
            funnel_sum = 0
            for row in funnel_rows:
                funnel_data1.append({'name': row[0], 'value': row[1]})
                funnel_sum += row[1]
            for row in funnel_rows:
                funnel_data2.append({'name': row[0], 'value': int(row[1]/funnel_sum * 100)})

            return render_template('province.html', cities=cities, province=province,
                                   bar_data=bar_data, bar_attr=bar_attr, bar_axis=bar_axis,
                                   pie_data=pie_data, pie_names=pie_names, pie_name=pie_name, pie_number=pie_number,
                                   radar_legend=radar_legend, radar_indi=radar_indi, radar_title=radar_title,
                                   radar_series=radar_series,
                                   number=number, number_format=number_format,
                                   funnel_data1=funnel_data1, funnel_data2=funnel_data2, funnel_name=funnel_name,
                                   funnel_title=funnel_title,
                                   bar_title=bar_title)
        else:
            return redirect(url_for('index'))
'''
点击工作 跳转到工作分析界面
'''
@ix.route('/job')
def job():
    if request.method == 'GET':
        if session.get('power') == None:
            session['power'] = -404
        print(request.args.get('j'))
        return render_template('job.html')
'''
点击学历 跳转到学历分析界面
'''

@ix.route('/edu')
def edu():
    if request.method == 'GET':
        if session.get('power') == None:
            session['power'] = -404
        print(request.args.get('e'))
        return render_template('edu.html')